* C:\Dropbox\Rehm\Data\OECD\SOCX\socx2018.do

* source: http://stats.oecd.org/wbos/Index.aspx?DataSetCode=SOCX_AGG
* see also: https://ec.europa.eu/eurostat/web/social-protection/data/database

cd D:\E\replications\BJPS2020\rawdata\macro\oecd_socx

/*
* Different measures are in different files:
e1:
PCT_GNI=In percentage of Gross National Income
PCT_NNI=In percentage of Net National Income
PCT_GOV=In percentage of Total General Government Expenditure

e2:
PPPH     =Per head, at current prices and current PPPs, in US dollars
PPPVH    =Per head, at constant prices (2010) and constant PPPs (2010), in US dollars
PCT_GDP  =In percentage of Gross Domestic Product

e3:
NCUR	=At current prices in national currency, in millions
NCST	=At constant prices (2010) in national currency, in millions
*/

clear
foreach j in 1 2 3 {
	insheet using "./SOCX_AGG_e`j'.csv", comma clear
	tempfile t_`j'
	save `t_`j''
}
clear
append using `t_1'
append using `t_2'
append using `t_3'

*keep if v2=="Public" 
*keep if v2=="Public and mandatory private"
*drop source
*drop typeofexpenditure typeofprogramme flags

kountry country, from(iso3c) to(cown)
rename _COWN_ ccode
rename country iso3c
labmask ccode, val(iso3c)
numlabel ccode, add force
tab iso3c if ccode==.
drop if ccode==.

drop branch
rename v4 branch
gen socx=""
replace socx="oldage" if branch=="Old age"
replace socx="survivors" if branch=="Survivors"
replace socx="agesurviv" if branch=="Old age and Survivors"
replace socx="inability" if branch=="Incapacity related"
replace socx="health" if branch=="Health"
replace socx="family" if branch=="Family"
replace socx="almp" if branch=="Active labour market programmes"
replace socx="unempl" if branch=="Unemployment"
replace socx="housing" if branch=="Housing"
replace socx="other" if branch=="Other social policy areas"
replace socx="all" if branch=="Total"

drop if socx==""
isid ccode year source branch typexp typrog unit

labmask typrog, val(typeofprogramme)
numlabel typrog, add
label list typrog

keep if typrog==0
isid ccode year source branch typexp unit
drop typrog typeofprogramme

keep if typeofexpenditure=="Total" // Benefits in kind & Cash benefits
drop typexp typeofexpenditure
isid ccode year source branch unit

rename v2 lsource
rename branch lsocx
rename measure lunit

keep ccode year source lsource socx unit lunit value

rename value socx_
reshape wide socx_, i(ccode year unit lunit source lsource) j(socx) string

label var socx_oldage  "1. Old Age"
label var socx_survivors "2. Survivors"
label var socx_agesurviv "1+2. Old age and Survivors"
label var socx_inability "3. Incapacity related benefits"
label var socx_health  "4. Health"
label var socx_family  "5. Family"
label var socx_almp   "6. Active labour market programmes"
label var socx_unempl  "7. Unemployment"
label var socx_housing "8. Housing"
label var socx_other  "9. Other"
label var socx_all   "Total public social expenditure"

/*
source:
20 Mandatory private
40 Net Public 
50 Net Total 
20_30 Private (Mandatory and Voluntary)
10 Public
30 Voluntary private
*/

drop lsource
rename socx_* socx_*_
reshape wide socx_*, i(ccode year unit lunit) j(source) string

foreach j in 10 20 30 20_30 40 50 {
	if "`j'"=="10"	 local t "Public"
	if "`j'"=="20" 	 local t "Mandatory private"
	if "`j'"=="20_30" local t "Private (Mandatory and Voluntary)"
	if "`j'"=="30"	 local t "Voluntary private"
	if "`j'"=="40" 	 local t "Net Public" 
	if "`j'"=="50" 	 local t "Net Total" 

	label var socx_oldage_`j'  "1. Old Age, `t'"
	label var socx_survivors_`j' "2. Survivors, `t'"
	label var socx_agesurviv_`j' "1+2. Old age and Survivors, `t'"
	label var socx_inability_`j' "3. Incapacity related benefits, `t'"
	label var socx_health_`j'  "4. Health, `t'"
	label var socx_family_`j'  "5. Family, `t'"
	label var socx_almp_`j'   "6. Active labour market programmes, `t'"
	label var socx_unempl_`j'  "7. Unemployment, `t'"
	label var socx_housing_`j' "8. Housing, `t'"
	label var socx_other_`j'  "9. Other, `t'"
	label var socx_all_`j'   "Total public social expenditure, `t'"
}

compress
note: C:\Dropbox\Rehm\Data\OECD\SOCX\socx2018.do
note: source: http://stats.oecd.org/wbos/Index.aspx?DataSetCode=SOCX_AGG
note: Created on `= c(current_date)'
save socx2018.dta, replace

